package com.xquant.pricing.utils;


import com.github.pagehelper.util.StringUtil;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class ExcelImportUtil<T> {

    private Class clazz;
    private Field fields[];

    public ExcelImportUtil(Class clazz) {
        this.clazz = clazz;
        fields = clazz.getDeclaredFields();
    }


    //todo 提取getWorkbook的方法
    public static Workbook getWorkbook(MultipartFile cmf) {
        List<String> list = new ArrayList<>();
        // 获得上传文件的文件名
        String fileName = cmf.getOriginalFilename();
        // 获得上传文件的文件名 去后缀
        String name = FilenameUtils.getBaseName(fileName);
        //文件类型
        String type = FilenameUtils.getExtension(fileName);
        InputStream is = null;
        try {
            is = cmf.getInputStream();
            Workbook workbook;
            if (type.toLowerCase().equals("xlsx")) {
                workbook = new XSSFWorkbook(is);
            } else {
                workbook = new HSSFWorkbook(is);
            }
            return workbook;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    /**
     * 校验模板是否合适
     * rowIndex  cellIndex  标题开始的行
     */
    public static String checkExcel(Workbook workbook, List titleList, int rowIndex) throws Exception {
        List<String> list = new ArrayList<>();

        Sheet sheet = workbook.getSheetAt(0);
        // 不准确
        Row row = sheet.getRow(rowIndex);
        for (int j = 0; j < row.getLastCellNum(); j++) {
            Cell cell = row.getCell(j);
            if (StringUtils.isBlank(cell.getStringCellValue())) {
                continue;
            }
            if (titleList.contains(cell.getStringCellValue())) {
                continue;
            }
            return "请选择正确模版导入！";
        }
        return null;
    }


    /**
     * 基于注解读取excel
     */
    public List<T> readExcel(Workbook workbook, int rowIndex) {
        List<T> list = new ArrayList<T>();
        T entity = null;
        try {
            Sheet sheet = workbook.getSheetAt(0);
            // 不准确
            int rowLength = sheet.getLastRowNum();
//            System.out.println(sheet.ge
//              ,tLastRowNum());
            Row headRow = sheet.getRow(rowIndex - 1);


            for (int rowNum = rowIndex; !isRowEmpty(sheet.getRow(rowNum)); rowNum++) {
                Row row = sheet.getRow(rowNum);
                entity = (T) clazz.newInstance();
//                System.out.println(row.getLastCellNum());
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    for (Field field : fields) {
                        if (field.isAnnotationPresent(ExcelAttribute.class)) {
                            field.setAccessible(true);
                            ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
                            if (ea.name().equals(getValue(headRow.getCell(j)))) {
                                field.set(entity, covertAttrType(field, cell));
                                continue;
                            }
                        }
                    }
                }
                list.add(entity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    /**
     * 类型转换 将cell 单元格格式转为 字段类型
     */
    private Object covertAttrType(Field field, Cell cell) throws Exception {
        String fieldType = field.getType().getSimpleName();
        if ("String".equals(fieldType)) {
            return getValue(cell);
        } else if ("Date".equals(fieldType)) {
            return new SimpleDateFormat("yyyy-MM-dd").parse(getValue(cell));
        } else if ("int".equals(fieldType) || "Integer".equals(fieldType)) {
            return Integer.parseInt(getValue(cell));
        } else if ("double".equals(fieldType) || "Double".equals(fieldType)) {
            return Double.parseDouble(getValue(cell));
        } else {
            return null;
        }
    }


    /**
     * 格式转为String
     *
     * @param cell
     * @return
     */
    public static String getValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getRichStringCellValue().getString().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date dt = DateUtil.getJavaDate(cell.getNumericCellValue());
                    return new SimpleDateFormat("yyyy-MM-dd").format(dt);
                } else {
                    // 防止数值变成科学计数法
                    String strCell = "";
                    Double num = cell.getNumericCellValue();
                    BigDecimal bd = new BigDecimal(num.toString());
                    if (bd != null) {
                        strCell = bd.toPlainString();
                    }
                    // 去除 浮点型 自动加的 .0
                    if (strCell.endsWith(".0")) {
                        strCell = strCell.substring(0, strCell.indexOf("."));
                    }
                    return strCell;
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }

    public String getCellValue(Workbook workbook, int rowIndex, int cellIndex) {
        Sheet sheet = workbook.getSheetAt(0);
        // 不准确
        int rowLength = sheet.getLastRowNum();
        return getValue(sheet.getRow(rowIndex).getCell(cellIndex));
    }

    //断空行的方法
    public static boolean isRowEmpty(Row firstRow) {
        if (firstRow == null || SysUtils.isVoid(firstRow)) {
            return true;
        }
        int count = 0;
        //单元格数量
        int rowCount = firstRow.getLastCellNum() - firstRow.getFirstCellNum();
        //判断多少个单元格为空
        for (int c = 0; c < rowCount; c++) {
            Cell cell = firstRow.getCell(c);
            if (cell == null || cell.getCellType() == CellType.BLANK || StringUtil.isEmpty((cell + "").trim())) {
                count += 1;
            }
        }
        if (count == rowCount) {
            return true;

        }
        return false;
    }

}
